package docker

//docker 安装mysql8
//注意： 因为mysql的版本不同，各种命令也不相同
//当有多个mysql容器启动时，只要有一个配置好，都可以登录，通过查看 select mysql.user,authentication_string,host from user;

/*

	目录：
		1.获取mysql,并查看版本
		2.docker run 运行容器
		3.使用mysql -u -p 登录容器

参考文档：
https://blog.csdn.net/zkzbhh/article/details/100214667
http://www.manongjc.com/detail/12-ursfoedugntgtcp.html
https://blog.csdn.net/lizz2276/article/details/111312815




-----------获取mysql,并查看版本-----------
		docker pull mysql
		docker inspect mysql | grep -i version

root@VM-4-8-debian:~# docker inspect mysql | grep -i version
                "GOSU_VERSION=1.12",
                "MYSQL_VERSION=8.0.27-1debian10"
        "DockerVersion": "20.10.7",
                "GOSU_VERSION=1.12",
                "MYSQL_VERSION=8.0.27-1debian10"
root@VM-4-8-debian:~#


-----------docker run 运行容器-----------

		docker run -it --privileged=true -d  --name mysql-master -p 3307:3306 \
		-v /home/zydh/docker/lib/mysql/mysql-files:/var/lib/mysql-files/  \
		-v /home/zydh/docker/etc/mysql-master/conf:/etc/mysql \
		-v /home/zydh/docker/volume/mysql-master/data:/var/lib/mysql \
		-v /home/zydh/logs/mysql-master/log:/var/log/mysql	 \
		-e MYSQL_ROOT_PASSWORD=root \
		mysql  --character-set-server=utf8mb4 --collation-server=utf8mb4_unicode_ci



-----------使用mysql -u -p 登录容器-----------
		docker ps  查看容器
           如果并没有查看到容器，使用docker ps -a ,发现容器启动失败，使用docker logs [容器id]  查看错误

		重开一个链接
		docker  exec -it [容器id] /bin/bash
		mysql -uroot -proot


root@VM-4-8-debian:/home/zydh/docker/etc/mysql-master/conf# docker ps
CONTAINER ID   IMAGE     COMMAND                  CREATED         STATUS         PORTS                                                                                      NAMES
58e0d8f142d9   mysql     "docker-entrypoint.s…"   6 seconds ago   Up 4 seconds   33060/tcp, 0.0.0.0:3307->3306/tcp, :::3307->3306/tcp                                       mysql-master
....

	当前错误在启动多个mysql时容易触发

root@VM-4-8-debian:/home/zydh/docker/etc/mysql-master/conf# docker exec -it 58e0d8f142d9 /bin/bash
root@58e0d8f142d9:/# mysql -uroot -proot
mysql: [Warning] Using a password on the command line interface can be insecure.
ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: YES)
root@58e0d8f142d9:/#


	解决：
	1.进入本地配置文件	/home/zydh/docker/etc/mysql-master/conf	目录下新建  my.cnf

root@VM-4-8-debian:/home/zydh/docker/etc/mysql-master/conf# touch my.cnf
root@VM-4-8-debian:/home/zydh/docker/etc/mysql-master/conf# vim my.cnf


[mysqld]
##设置使用命令跳过输入密码过程,在mysqld下添加，使用完毕后，删除
skip-grant-tables
skip-networking


	2.编辑完配置文件后，重启mysql-master

root@VM-4-8-debian:/home/zydh/docker/etc/mysql-master/conf# docker restart mysql-master
mysql-master
root@VM-4-8-debian:/home/zydh/docker/etc/mysql-master/conf#

	3.进入docker mysql容器内部

root@VM-4-8-debian:/home/zydh/docker/etc/mysql-master/conf# docker exec -it 58e0d8f142d9 /bin/bash
root@58e0d8f142d9:/#
root@58e0d8f142d9:/# mysql -uroot -p
Enter password:  	【空格回车】
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 7
Server version: 8.0.27 MySQL Community Server - GPL
.....
mysql>




	4.使用mysql，查询关键
	use mysql;
	select user,authentication_string,host from user;
		#上面的root用户如果是localhost，只能localhost连接，不能远程连接，下面设置密码报错


mysql> use mysql;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> select user,authentication_string,host from user;
+------------------+------------------------------------------------------------------------+-----------+
| user             | authentication_string                                                  | host      |
+------------------+------------------------------------------------------------------------+-----------+
| root             | 											                            | localhost |
| mysql.infoschema | $A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED | localhost |
| mysql.session    | $A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED | localhost |
| mysql.sys        | $A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED | localhost |
+------------------+------------------------------------------------------------------------+-----------+
4 rows in set (0.00 sec)

mysql>


	#上面的root用户如果是localhost，只能localhost连接，不能远程连接，下面设置密码报错

mysql> ALTER USER 'root'@'%' IDENTIFIED WITH mysql_native_password BY 'xxxxxx';
ERROR 1396 (HY000): Operation ALTER USER failed for 'root'@'%'

	#修改localhost为%
	update user set host='%' where user='root';

mysql> update user set host='%' where user='root';
Query OK, 1 row affected (0.14 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select user,authentication_string,host from user;
+------------------+------------------------------------------------------------------------+-----------+
| user             | authentication_string                                                  | host      |
+------------------+------------------------------------------------------------------------+-----------+
| root             | *6C362347EBEAA7DF44F6D34884615A35095E80EB                              | %         |
| mysql.infoschema | $A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED | localhost |
| mysql.session    | $A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED | localhost |
| mysql.sys        | $A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED | localhost |
+------------------+------------------------------------------------------------------------+-----------+
4 rows in set (0.00 sec)

mysql>


	5.更新为空

	update user set authentication_string='' where user='root';
	flush privileges;


mysql> update user set authentication_string='' where user='root';
Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)

mysql> exit
Bye




	6. 退出mysql，把第一步的skip-grant-tables注释。再重启mysql
		exit
		exit
		vim my.cnf
		docker restart mysql-master


	7.使用使用 root用户，登录；，回车
	8.修改root密码
			执行这步，需要查看自己root对应的host是什么，上面已经把root的host由 localhost 改为了 %
			如果报错ERROR 1396 (HY000): Operation ALTER USER failed for 'root'@'%'
			此处就是未注意'root'@'%'到底是 %  还是 localhost

		alter user 'root'@'localhost' IDENTIFIED BY 'self_psd'；
		or
		alter user 'root'@'%' IDENTIFIED BY 'self_psd'；
		flush privileges；


mysql> alter user 'root'@'%' IDENTIFIED BY '123456';
Query OK, 0 rows affected (0.00 sec)
or
mysql> alter user 'root'@'%' identified with mysql_native_password by '123456';
Query OK, 0 rows affected (0.01 sec)
mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)





		9.授权
		grant all privileges on *.* to root@'%' identified by 'rootroot';
		flush privileges;
			报错：,因为版本的问题，用的是mysql8
			ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'identified by 'rootroot'' at line 1


		mysql8使用

		grant all privileges on *.* to 'root'@'%';
		flush privileges;


		MySQL 8.0已经不支持下面这种命令写法
		grant all privileges on *.* to root@"%" identified by ".";

		正确的写法是先创建用户
		CREATE USER 'root'@'%' IDENTIFIED BY 'Hadoop3!';
		再给用户授权
		grant all privileges on *.* to 'root'@'%' ;

		#添加远程登录用户
		CREATE USER 'liaozesong'@'%' IDENTIFIED WITH mysql_native_password BY 'Lzslov123!';
		GRANT ALL PRIVILEGES ON *.* TO 'liaozesong'@'%';



mysql> grant all privileges on *.* to root@'%' identified by 'rootroot';
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'identified by 'rootroot'' at line 1
mysql>

mysql> GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' IDENTIFIED BY 'rootroot' WITH GRANT OPTION;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'IDENTIFIED BY 'rootroot' WITH GRANT OPTION' at line 1
mysql> GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' IDENTIFIED BY 'rootroot';
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'IDENTIFIED BY 'rootroot'' at line 1
mysql> grant all privileges on *.* to 'root'@'%' identified by 'rootroot';
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'identified by 'rootroot'' at line 1
mysql> GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' IDENTIFIED BY 'rootroot' WITH GRANT OPTION;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'IDENTIFIED BY 'rootroot' WITH GRANT OPTION' at line 1
mysql> GRANT all ON . TO 'root'@'%' IDENTIFIED BY 'rootroot';
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '. TO 'root'@'%' IDENTIFIED BY 'rootroot'' at line 1
mysql> mysql version;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'mysql version' at line 1
mysql> select version();
+-----------+
| version() |
+-----------+
| 8.0.27    |
+-----------+
1 row in set (0.00 sec)

--------------


mysql> grant all privileges on *.* to 'root'@'%';
ERROR 1410 (42000): You are not allowed to create a user with GRANT
mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)

mysql>
mysql> grant all privileges on *.* to 'root'@'%';
Query OK, 0 rows affected (0.01 sec)

mysql>
mysql>
mysql> flush privileges;
Query OK, 0 rows affected (0.01 sec)

mysql>
mysql>
mysql> exit
Bye



root@712253264da3:/# mysql -uroot -prootroot
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 9
Server version: 8.0.27 MySQL Community Server - GPL

Copyright (c) 2000, 2021, Oracle and/or its affiliates.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql>


mysql>  select user,authentication_string,host from user;
+------------------+------------------------------------------------------------------------+-----------+
| user             | authentication_string                                                  | host      |
+------------------+------------------------------------------------------------------------+-----------+
| root             | $A$005$Qw"ih/pwO_t
                                       ^MyEyHqklYHgTZo.tcBBxFXu/xLq.k1pWPKk0XH1YVeSfB | %         |
| mysql.infoschema | $A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED | localhost |
| mysql.session    | $A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED | localhost |
| mysql.sys        | $A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED | localhost |
| root             | $A$005$[]f_%zjd@q;_cWn%j2xuJeuPfKNysokQt1QO71nYG6bmT2U260n/ul4MTP. | localhost |
+------------------+------------------------------------------------------------------------+-----------+
5 rows in set (0.00 sec)



*/
